package com.intelligentmeterreadingsys.waterlist.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;


import com.intelligentmeterreadingsys.BaseDao.BaseDao;
import com.intelligentmeterreadingsys.povo.UseWaterInfoPoVo;
import com.intelligentmeterreadingsys.povo.UserWaterVO;
import com.intelligentmeterreadingsys.povo.UserinfoVO;
import com.intelligentmeterreadingsys.povo.WaterInfoVO;
import com.intelligentmeterreadingsys.povo.WaterlistPoVo;
import com.intelligentmeterreadingsys.util.Model;
import com.intelligentmeterreadingsys.util.SqlUtil;

import dbhelper.DbTableName;

/**
 * 表册查询
 * @author king
 *
 */
public class WaterListDaoImpl extends BaseDao implements IWaterListDao {
	
	
	private SQLiteDatabase db;

	public WaterListDaoImpl(Context context) {
		super(context);
		this.db=super.getDb();
		
		
	}

	/**
	 * 批量添加水表信息
	 */
	@Override
	public boolean insertAllWaterInfo(List<ContentValues> list) {
	  boolean b=super.insertALL(list, DbTableName.WATERINFO);
		return b;
	}
    
	/**
	 * 删除所有水表信息
	 */
	@Override
	public boolean delectAllWaterInfo() {
		boolean b=super.delectAll(DbTableName.WATERINFO);
		return b;
	}

	/**
	 * 查询一个水表信息
	 */
	@Override
	public WaterInfoVO queryWaterInfo(WaterInfoVO water) {
		
		return null;
	}

	/**
	 * 抄表时更新数据
	 */
	@Override
	public boolean modify(WaterInfoVO water) {
		ContentValues values=new ContentValues();
		
		values.put("thismonthwater", water.getThismonthwater());
	    boolean b=super.modify(water.getId(), DbTableName.WATERINFO, values);
		
		return b;
	}
	
	/**
	 * 关闭连接
	 */
	public void close(){
		if(db!=null&&db.isOpen()){
			db.close();
		}
		super.close();
	}

	/**
	 * 查询表册所有用户
	 */
	@Override
	public int queryAllUser() {
		int number=0;
		String sql="select _id from userinfo where ismultiplecopy='0'";
		Cursor cursor=db.rawQuery(sql, null);
		
		number=cursor.getCount();
	
		if(cursor!=null){
			cursor.close();
		}
		return number;
	}

	 /**
	  * 查询所有水表
	  */
	@Override
	public int queryAllWater() {
		int number=0;
		String sql="Select _id From  waterinfo where ismultiplecopy='0'";
		Cursor cursor=db.rawQuery(sql, null);
		number=cursor.getCount();
		if(cursor!=null){
			cursor.close();
		}
		return number;
	}

	@Override
	public int queryAllNoRedUser() {
		
		return 0;
	}

	
	/**
	 * 查询没有抄的水表
	 */
	@Override
	public int queryAllNoRedWater() {
		int number=0;
		String sql="Select _id From  waterinfo where iscopystatus='未抄' or iscopystatus='二次抄表' and ismultiplecopy='0'";
		Cursor cursor=db.rawQuery(sql, null);
		number=cursor.getCount();
		if(cursor!=null){
			cursor.close();
		}
		return number;
	}

	
	/**
	 * 查询所有报警水表
	 */
	@Override
	public int queryAlarmWater() {
		int number=0;
		String sql="select _id from waterinfo w where (w.[thismonthwater]-w.[lastmonthwater])<w.[lowalarmlimit] or  (w.[thismonthwater]-w.[lastmonthwater])>w.[alarmlimit]" +
				"  and iscopystatus='已抄' and ismultiplecopy='0'";
		Cursor cursor=db.rawQuery(sql, null);
		number=cursor.getCount();
		if(cursor!=null){
			cursor.close();
		}
		return number;
	}

	
  

	/**
	 * 通过用户编号查询水表信息
	 */
	@Override
	public List<WaterInfoVO> queryWaterInfo(String usernumber) {
		List<WaterInfoVO> list=null;
	    String sql="select * from waterinfo where ismultiplecopy='0'";
	    if(!TextUtils.isEmpty(usernumber)){
	    	sql=sql+" and usernumber="+usernumber;
	    }
	    System.out.println(sql);
	    Cursor cursor= db.rawQuery(sql, null);
	    if(cursor!=null){
	    	list=new ArrayList<WaterInfoVO>();
	    }else{
	    	return null;
	    }
	    while(cursor.moveToNext()){
	    	WaterInfoVO water=new WaterInfoVO();
	    	water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
	    	water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
	    	water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
	    	water.setLastdate(cursor.getString(cursor.getColumnIndex("lastdate")));
	    	water.setWaterproperties(cursor.getString(cursor.getColumnIndex("waterproperties")));
	    
	    	water.setWateradds(cursor.getString(cursor.getColumnIndex("wateradds")));
	    	water.setThisdate(cursor.getString(cursor.getColumnIndex("thisdate")));
	    	list.add(water);
	    	
	    }
	    if(cursor!=null){
			cursor.close();
		}
	  
		return list;
	}

	/**
	 * 通过水表编号查询水表信息
	 */
	@Override
	public WaterInfoVO queryWater(String waterNumber) {
		if(TextUtils.isEmpty(waterNumber)){
			return null;
		}
		String sql="select _id,wateradds,latitude,longitude from waterinfo where" +
				" waternumber="+waterNumber+" and ismultiplecopy='0'";
		Cursor cursor=db.rawQuery(sql, null);
		WaterInfoVO water=new WaterInfoVO();
		water.setWaternumber(waterNumber);
		if(cursor.moveToNext()){
			water.setWateradds(cursor.getString(cursor.getColumnIndex("wateradds")));
			water.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
			water.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
		}
		if(cursor!=null){
			cursor.close();
		}
		return water;
		
		
	}
    
	/**
	 * 添加历史用水信息
	 * @param values
	 * @return
	 */
	public boolean insertUseWater(List<ContentValues> values){
		
		return super.insertALL(values, DbTableName.USEWATERINFO);
		
	}
    
	/**
	 * 查询表册信息
	 */
	@Override
	public List<UserWaterVO> queryWaterlist(String username, String usernumber,
			String waterproperties, String wateradds, String bandingstatus,
			String copystatus) {
		StringBuffer sb=new StringBuffer();
		sb.append("select w.[_id] as waterid,w.[waternumber],w.[latitude],w.[waterproperties],w.[lastmonthwater],w.[lastdate], ")
		.append("w.[iscopystatus],w.[thismonthwater],w.[longitude],w.[wateradds],u.[username],u.[usernumber]")
		.append(" from waterinfo w,userinfo u where w.[usernumber]=u.[usernumber] and w.[ismultiplecopy]='0'");
		sb.append(" and u.[ismultiplecopy]='0'");
	
		if(username!=null&&!username.trim().equals("")){
		
			sb.append(" and u.[username]='").append(username).append("'");
		}
		if(usernumber!=null&&!usernumber.trim().equals("")){
			sb.append(" and u.[usernumber]='").append(usernumber).append("'");
		}
		if(waterproperties!=null&&!waterproperties.trim().equals("")){
			sb.append(" and w.[waterproperties]='").append(waterproperties).append("'");
		}
		if(wateradds!=null&&!wateradds.trim().equals("")){
			sb.append(" and w.[wateradds] like'%").append(wateradds).append("%'");
		}
		if(bandingstatus!=null&&!bandingstatus.trim().equals("")){
			if(bandingstatus.equals("2")){
				sb.append(" and w.[bandingstatus]='").append(bandingstatus).append("'");
			}else{
				sb.append(" and w.[bandingstatus] in (1,3,4)");
			}
			
		}
		if(copystatus!=null&&!copystatus.trim().equals("")){
			sb.append(" and w.[iscopystatus]='").append(copystatus).append("'");
		}
		String sql=sb.toString();
		System.out.println(sql);
		List<UserWaterVO> list=new ArrayList<UserWaterVO>();
		Cursor cursor=db.rawQuery(sql, null);
		while(cursor.moveToNext()){
			UserWaterVO water=new UserWaterVO();
			water.setName(cursor.getString(cursor.getColumnIndex("username")));
			water.setNumber(cursor.getString(cursor.getColumnIndex("usernumber")));
			water.setWateradds(cursor.getString(cursor.getColumnIndex("wateradds")));
			water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
			water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
			water.setLastdate(cursor.getString(cursor.getColumnIndex("lastdate")));
			String iscopy=cursor.getString(cursor.getColumnIndex("iscopystatus"));
			if(iscopy.equals("已抄")){
				water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
			}else{
				water.setThismonthwater(0.0);
			}
		
			water.setCopystatus(cursor.getString(cursor.getColumnIndex("iscopystatus")));
			water.setWaterproperties(cursor.getString(cursor.getColumnIndex("waterproperties")));
			water.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
			water.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
			water.setWaterid(cursor.getString(cursor.getColumnIndex("waterid")));
			list.add(water);
			
			
		}
		if(cursor!=null){
			cursor.close();
		}
		return list;
	}

	/**
	 * 查询二两次表数
	 */
	@Override
	public int queryTowCopy() {
		String sql="select _id from waterinfo where iscopystatus='二次抄表' and ismultiplecopy='0'";
		Cursor cursor=db.rawQuery(sql, null);
		int i=cursor.getCount();
		if(cursor!=null){
			cursor.close();
		}
		return i;
	}
    
	/**
	 * 查询已抄但未上传数
	 */
	@Override
	public int queryNoUpload() {
		String sql="select _id from waterinfo where isupload='0' and iscopystatus='已抄' and ismultiplecopy='0'";
		Cursor cursor=db.rawQuery(sql, null);
		if(cursor==null){
			return 0;
		}
		int i=cursor.getCount();
		if(cursor!=null){
			cursor.close();
		}
		return i;
	}
	
	
    /**
     * 查询用户
     */
	@Override
	public List<UserinfoVO> queryUserInfo(String name, String adds,
			String phone, String usernumber, String usertype) {
		StringBuffer sb=new StringBuffer("select u.[_id],u.[username],u.[useradds],u.[usertype],u.[phone],u.[usernumber],u.[userstatus]");
	    sb.append(" from userinfo u where ismultiplecopy='0'");
		if(!TextUtils.isEmpty(name)){
			sb.append(" and u.[username]='").append(name).append("'");
		}
		if(!TextUtils.isEmpty(adds)){
			sb.append(" and u.[useradds] like '").append(adds).append("'");
		}
		if(!TextUtils.isEmpty(phone)){
			sb.append(" and u.[phone]='").append(phone).append("'");
		}
		if(!TextUtils.isEmpty(usernumber)){
			sb.append(" and u.[usernumber]='").append(usernumber).append("'");
		}
		if(!TextUtils.isEmpty(usertype)&&!usertype.equals("全部")){
			
			sb.append(" and u.[usertype]='").append(usertype).append("'");
		}
		String sql=sb.toString();
		System.out.println(sql);
		Cursor cursor=db.rawQuery(sql, null);
		List<UserinfoVO> list=new ArrayList<UserinfoVO>();
		while(cursor.moveToNext()){
			UserinfoVO user=new UserinfoVO();
			user.setId(cursor.getInt(cursor.getColumnIndex("_id")));
			user.setName(cursor.getString(cursor.getColumnIndex("username")));
			user.setAdds(cursor.getString(cursor.getColumnIndex("useradds")));
			user.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
			user.setType(cursor.getString(cursor.getColumnIndex("usertype")));
			user.setNumber(cursor.getString(cursor.getColumnIndex("usernumber")));
			user.setStatus(cursor.getString(cursor.getColumnIndex("userstatus")));
			list.add(user);
		}
		if(cursor!=null){
			cursor.close();
		}
		return list;
	}

	/**
	 * 查询用户类型
	 */
	@Override
	public String[] queryUserType() {
		String[] type=null;
		String sql="select * from type where status=1";
		
		Cursor cursor=db.rawQuery(sql, null);
		type=new String[cursor.getCount()+1];
		type[0]="全部";
		int i=1;
		while(cursor.moveToNext()){
			type[i]=cursor.getString(cursor.getColumnIndex("name"));
			i++;
		}
		if(cursor!=null){
			cursor.close();
		}
		return type;
	}

	

	
	/**
	 * 设置成二次抄表
	 */
	@Override
	public boolean Modifytowcopy(String number) {
		ContentValues value=new ContentValues();
		value.put("iscopystatus", "二次抄表");
	   int id= db.update(DbTableName.WATERINFO, value, "waternumber=? and ismultiplecopy=?", new String[]{number,"0"});
	   if(id>0){
		   System.out.println("二次抄表"+number);
		   return true;
	   }
		return false;
	}

	@Override
	public boolean delectWaterList() {
		
		db.delete(DbTableName.USEWATERINFO, "ismultiplecopy=?", new String[]{"0"});
		db.delete(DbTableName.USERINFO, "ismultiplecopy=?", new String[]{"0"});
		super.delectAll(DbTableName.TYPE);
		db.delete(DbTableName.WATERINFO, "ismultiplecopy=?", new String[]{"0"});
		super.delectAll(DbTableName.WATERLIST);
		int id=db.delete(DbTableName.RESOURCES, "status=?", new String[]{"2"});
		
		if(id>0){
			return true;
		}
		;
		return false;
	}

	/**
	 * 通过水表ID查询
	 * @param waterid
	 * @return
	 */
	public UserWaterVO queryOneWaterlist(String waterid){
		if(TextUtils.isEmpty(waterid)){
			return null;
		}
		StringBuffer sb=new StringBuffer();
		sb.append("select w.[_id] as waterid,w.[adds],w.[waternumber],w.[latitude],w.[waterproperties],w.[lastmonthwater],w.[lastdate], ")
		.append("w.[iscopystatus],w.[thismonthwater],w.[longitude],w.[wateradds],u.[username],u.[usernumber],u.[phone],w.[isupload]")
		.append(" from waterinfo w,userinfo u where w.[usernumber]=u.[usernumber] and w.[ismultiplecopy]='0'");
		sb.append(" and u.[ismultiplecopy]='0'");
		sb.append(" and w.[_id]=").append(waterid);
		UserWaterVO water=null;
		String sql=sb.toString();
		System.out.println("one watersql"+sql);
		Cursor cursor=db.rawQuery(sql, null);
		if(cursor.moveToNext()){
			 water=new UserWaterVO();
			water.setName(cursor.getString(cursor.getColumnIndex("username")));
			water.setNumber(cursor.getString(cursor.getColumnIndex("usernumber")));
			water.setWateradds(cursor.getString(cursor.getColumnIndex("wateradds")));
			water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
			water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
			water.setLastdate(cursor.getString(cursor.getColumnIndex("lastdate")));
			String iscopy=cursor.getString(cursor.getColumnIndex("iscopystatus"));
			if(iscopy.equals("已抄")){
				water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
			}else{
				water.setThismonthwater(0.0);
			}
		
			water.setCopystatus(cursor.getString(cursor.getColumnIndex("iscopystatus")));
			water.setWaterproperties(cursor.getString(cursor.getColumnIndex("waterproperties")));
			water.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
			water.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
			water.setWaterid(cursor.getString(cursor.getColumnIndex("waterid")));
			water.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
			water.setIsupload(cursor.getString(cursor.getColumnIndex("isupload")));
			water.setLocationadds(cursor.getString(cursor.getColumnIndex("adds")));
		}
		
		if(cursor!=null){
			cursor.close();
		}
		return water;
		
		
		
	}

	
	/**
	 * 查询水表历史用水量
	 */
	@Override
	public List<UseWaterInfoPoVo> queryUseWater(String waterid, String type) {
		StringBuffer sb=new StringBuffer("select u.[waternumber],u.[month],u.[watercount]  ");
		sb.append("from  usewaterinfo u,waterinfo w where w.[waternumber]=u.[waternumber]");
		
		sb.append(" and w.[_id]=").append(waterid);
		sb.append(" and w.[ismultiplecopy]='0' and u.[ismultiplecopy]='0'");
		sb.append(" order by u.[month]");
		Cursor cursor=db.rawQuery(sb.toString(), null);
		List<UseWaterInfoPoVo> list=new ArrayList<UseWaterInfoPoVo>();
		String month="";
		
		while(cursor.moveToNext()){
			String mo=cursor.getString(cursor.getColumnIndex("month"));
			Double count=Double.parseDouble(cursor.getString(cursor.getColumnIndex("watercount")));
			
			if(mo.equals(month)){
				System.out.println("月份相同");
				UseWaterInfoPoVo use=list.get(list.size()-1);
				use.setWatercount(use.getWatercount()+count);
				list.remove(list.size()-1);
				list.add(use);
				month=mo;
			}else{
				System.out.println("月份不同");
				UseWaterInfoPoVo usewater=new UseWaterInfoPoVo();
				usewater.setMoth(mo);
				usewater.setWatercount(count);
				
				list.add(usewater);
				month=mo;
				
			}
		
	}
		if(cursor!=null){
			cursor.close();
		}
		return list;
		
	
	}
	
	/**
	 * 分页查询水表信息
	 * @param model
	 * @param username
	 * @param usernumber
	 * @param waterproperties
	 * @param wateradds
	 * @param bandingstatus
	 * @param copystatu
	 * @return
	 */
	public List<UserWaterVO> queryWaterlist(Model model,String waterlist, String usernumber,
			String waterproperties, String wateradds, String bandingstatus,
			String copystatus){
		StringBuffer sb=new StringBuffer();
		sb.append("select w.[_id] as waterid,w.[waternumber],w.[latitude],w.[waterproperties],w.[lastmonthwater],w.[lastdate], ")
		.append("w.[iscopystatus],w.[thismonthwater],w.[longitude],w.[wateradds],u.[username],u.[usernumber]")
		.append(" from waterinfo w,userinfo u where w.[usernumber]=u.[usernumber] and w.[ismultiplecopy]='0'");
		sb.append(" and u.[ismultiplecopy]='0'");
	
		if(waterlist!=null&&!waterlist.trim().equals("")&&!waterlist.equals("0")){
		
			sb.append(" and w.[waterlistid]='").append(waterlist).append("'");
		}
		if(usernumber!=null&&!usernumber.trim().equals("")){
			sb.append(" and u.[usernumber]='").append(usernumber).append("'");
		}
		if(waterproperties!=null&&!waterproperties.trim().equals("")){
			sb.append(" and w.[waterproperties]='").append(waterproperties).append("'");
		}
		if(wateradds!=null&&!wateradds.trim().equals("")){
			sb.append(" and w.[wateradds] like'%").append(wateradds).append("%'");
		}
		if(bandingstatus!=null&&!bandingstatus.trim().equals("")){
			if(bandingstatus.equals("2")){
				sb.append(" and w.[bandingstatus]='").append(bandingstatus).append("'");
			}else{
				sb.append(" and w.[bandingstatus] in (0,1,3)");
			}
			
		}
		if(copystatus!=null&&!copystatus.trim().equals("")){
			sb.append(" and w.[iscopystatus]='").append(copystatus).append("'");
		}
		String sql=sb.toString();
		System.out.println(sql+"条件查询表册");
		Cursor cursor=SqlUtil.getAllItems(db, (model.getIndex()-1)*model.getView_Count(), model.getView_Count(), sql);
		List<UserWaterVO> list=new ArrayList<UserWaterVO>();
		while(cursor.moveToNext()){
           
			UserWaterVO water=new UserWaterVO();
			water.setName(cursor.getString(cursor.getColumnIndex("username")));
			water.setNumber(cursor.getString(cursor.getColumnIndex("usernumber")));
			water.setWateradds(cursor.getString(cursor.getColumnIndex("wateradds")));
			water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
			water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
			water.setLastdate(cursor.getString(cursor.getColumnIndex("lastdate")));
			String iscopy=cursor.getString(cursor.getColumnIndex("iscopystatus"));
			if(iscopy.equals("已抄")){
				water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
			}else{
				water.setThismonthwater(0.0);
			}
		
			water.setCopystatus(cursor.getString(cursor.getColumnIndex("iscopystatus")));
			water.setWaterproperties(cursor.getString(cursor.getColumnIndex("waterproperties")));
			water.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
			water.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
			water.setWaterid(cursor.getString(cursor.getColumnIndex("waterid")));
			list.add(water);		
		}
		if(cursor!=null){
			cursor.close();
		}
				return list;
		
	}
	
	/**
	 * 得到总页数
	 * @param username
	 * @param usernumber
	 * @param waterproperties
	 * @param wateradds
	 * @param bandingstatus
	 * @param copystatus
	 * @return
	 */
	public int queryTotalPage(String waterlist, String usernumber,
			String waterproperties, String wateradds, String bandingstatus,
			String copystatus){
		int page=0;
		StringBuffer sb=new StringBuffer();
		sb.append("select w.[_id] as waterid")
		.append(" from waterinfo w,userinfo u where w.[usernumber]=u.[usernumber] and w.[ismultiplecopy]='0'");
		sb.append(" and u.[ismultiplecopy]='0'");
	
		if(waterlist!=null&&!waterlist.trim().equals("")&&!waterlist.equals("0")){
			
			sb.append(" and w.[waterlistid]='").append(waterlist).append("'");
		}
		if(usernumber!=null&&!usernumber.trim().equals("")){
			sb.append(" and u.[usernumber]='").append(usernumber).append("'");
		}
		if(waterproperties!=null&&!waterproperties.trim().equals("")){
			sb.append(" and w.[waterproperties]='").append(waterproperties).append("'");
		}
		if(wateradds!=null&&!wateradds.trim().equals("")){
			sb.append(" and w.[wateradds] like'%").append(wateradds).append("%'");
		}
		if(bandingstatus!=null&&!bandingstatus.trim().equals("")){
			if(bandingstatus.equals("2")){
				sb.append(" and w.[bandingstatus]='").append(bandingstatus).append("'");
			}else{
				sb.append(" and w.[bandingstatus] in (0,1,3)");
			}
			
		}
		if(copystatus!=null&&!copystatus.trim().equals("")){
			sb.append(" and w.[iscopystatus]='").append(copystatus).append("'");
		}
		String sql=sb.toString();
		int k=super.getCount(sql);
		page=k%10>0?k/10+1:k/10;
		return page;
		
	}
	
	
	/**
	 * 查询所有表册信息
	 */
	@Override
	public Map<String, String> queryWaterList() {
		Map<String, String> list=new HashMap<String, String>();
		String sql="select * from "+DbTableName.WATERLIST;
		Cursor cursor=db.rawQuery(sql, null);
		while(cursor.moveToNext()){
		
			list.put(cursor.getString(cursor.getColumnIndex("name")),cursor.getString(cursor.getColumnIndex("number")) );
		}
		if(cursor!=null){
			cursor.close();
		}
		return list;
	}
	
}
